﻿using Mysoft.QuickCode.NPOI.HSSF.UserModel;
using Mysoft.QuickCode.NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

namespace Mysoft.QuickCode.Office.Excel
{
    public class TableTool
    {
        public ISheet Sheet { private set; get; }
        public DataTable Table { private set; get; }

        private ICellStyle DefaultHeaderCellStyle { set; get; }
        private ICellStyle DefaultBodyCellStyle { set; get; }

        private List<KeyValuePair<string, string>> ColumnsMapping { set; get; }

        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="table">数据表</param>
        /// <param name="sheetName">Sheet电子表名称</param>
        public TableTool(DataTable table, string sheetName)
        {
            IWorkbook workbook = new HSSFWorkbook();

            this.Table = table;
            this.Sheet = (string.IsNullOrEmpty(sheetName)) ? workbook.CreateSheet() : workbook.CreateSheet(sheetName);

            //默认单元格尺寸
            this.Sheet.DefaultColumnWidth = 20;
            this.Sheet.DefaultRowHeight = 20 * 15;

            //默认单元格样式
            //表头
            this.DefaultHeaderCellStyle = this.Sheet.Workbook.CreateCellStyle();
            this.DefaultHeaderCellStyle.Alignment = HorizontalAlignment.Center;
            this.DefaultHeaderCellStyle.VerticalAlignment = VerticalAlignment.Center;
            this.DefaultHeaderCellStyle.FillPattern = FillPattern.SolidForeground;
            this.DefaultHeaderCellStyle.FillForegroundColor = Mysoft.QuickCode.NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            //表体
            this.DefaultBodyCellStyle = this.Sheet.Workbook.CreateCellStyle();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="table"></param>
        /// <param name="sheetName"></param>
        /// <param name="columnsMapping">
        /// Table列的字段->中文映射关系。如果传入此参数，则在生成Excel时，只会按顺序生成映射中存在的字段。
        /// 原字段名称为Key，映射后的字段名称为Value。Excel的列名称会使用映射名称。
        /// </param>
        public TableTool(DataTable table, string sheetName, List<KeyValuePair<string, string>> columnsMapping)
            : this(table, sheetName)
        {
            this.ColumnsMapping = columnsMapping;
        }

        /// <summary>
        /// 将DataTable转换为Excel流
        /// </summary>
        /// <param name="table">DataTable</param>
        /// <param name="sheetName">工作表名称，为空则自动生成</param>
        /// <returns></returns>
        public MemoryStream GetExcelStream()
        {
            int sheetRowIndex = 0, sheetCellIndex = 0;

            //Excel表头
            IRow sheetRow = Sheet.CreateRow(sheetRowIndex++);
            sheetRow.Height = this.Sheet.DefaultRowHeight;  //表头默认高度
            if (this.ColumnsMapping == null)
            {
                foreach (DataColumn col in this.Table.Columns)
                {
                    ICell cell = sheetRow.CreateCell(sheetCellIndex++, CellType.String); //创建表头单元格
                    cell.SetCellValue(col.ColumnName);
                    cell.CellStyle = this.DefaultHeaderCellStyle;
                }

                //Excel数据
                foreach (DataRow row in this.Table.Rows)
                {
                    sheetCellIndex = 0;
                    IRow sr = this.Sheet.CreateRow(sheetRowIndex++);
                    sr.Height = this.Sheet.DefaultRowHeight;
                    foreach (DataColumn col in this.Table.Columns)
                    {
                        ICell cell = sr.CreateCell(sheetCellIndex++, CellType.String);
                        cell.SetCellValue(row[col.ColumnName].ToString());
                        cell.CellStyle = this.DefaultBodyCellStyle;
                    }
                }
            }
            else
            {
                foreach (KeyValuePair<string, string> kv in this.ColumnsMapping)
                {
                    ICell cell = sheetRow.CreateCell(sheetCellIndex++, CellType.String); //创建表头单元格
                    cell.SetCellValue(kv.Value);    //按映射名称生成表头
                    cell.CellStyle = this.DefaultHeaderCellStyle;
                }

                //Excel数据
                //判断是否需要树形结构分级？
                bool isTreeType = this.Table.Columns.Contains("_level_") && this.Table.Columns.Contains("_level_field_");
                Func<int, string> spaces = new Func<int, string>((int level) =>
                {
                    string s = string.Empty;
                    while (level-- > 0)
                    {
                        s += "    ";
                    }
                    return s;
                });
                foreach (DataRow row in this.Table.Rows)
                {
                    sheetCellIndex = 0;
                    IRow sr = this.Sheet.CreateRow(sheetRowIndex++);
                    sr.Height = this.Sheet.DefaultRowHeight;
                    foreach (KeyValuePair<string, string> kv in this.ColumnsMapping)
                    {
                        ICell cell = sr.CreateCell(sheetCellIndex++, CellType.String);
                        if (isTreeType && kv.Key.Equals(row["_level_field_"].ToString(), StringComparison.OrdinalIgnoreCase))
                        {
                            cell.SetCellValue(spaces(Convert.ToInt32(row["_level_"])) + row[kv.Key].ToString());  //只输出有映射名称的字段值
                        }
                        else
                        {
                            cell.SetCellValue(row[kv.Key].ToString());  //只输出有映射名称的字段值
                        }
                        cell.CellStyle = this.DefaultBodyCellStyle;
                    }
                }
            }

            MemoryStream stream = new MemoryStream();
            this.Sheet.Workbook.Write(stream);
            return stream;
        }
    }
}
